/*
Name:web_site
Author: Terry Dong
Description: data access layer
*/
using System;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Reflection;
using System.Data.Common;
using Gammar.Digger.SystemFramework;
using Gammar.Digger.Entity;
using Gammar.Digger.IDAL;
using Gammar.Digger.Data;
namespace Gammar.Digger.DALSqlServer
{
    public partial class web_site : Iweb_site
    {
        private DbSqlServer helper;

        #region constructors
        /// <summary>
        /// default constructor with the defaultConn
        /// </summary>
        public web_site()
        {
            helper = new DbSqlServer(ConnectionString.SqlServer_GPriceCollect);
        }

        /// <summary>
        /// default constructor with the defaultConn
        /// </summary>
        public web_site(string connString)
        {
            helper = new DbSqlServer(connString);
        }
        #endregion

        #region IDataBase<web_site>

        public int Add(web_siteEntity entity)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("INSERT INTO web_site(");
            strSql.Append("name,description,category_id)");
            strSql.Append(" VALUES (");
            strSql.Append("@name,@description,@category_id)");
            SqlParameter[] parameters = {
					new SqlParameter("@name",SqlDbType.VarChar),
					new SqlParameter("@description",SqlDbType.VarChar),
					new SqlParameter("@category_id",SqlDbType.UniqueIdentifier)
					};
            parameters[0].Value = entity.name;
            parameters[1].Value = entity.description;
            parameters[2].Value = entity.category_id;

            return helper.ExecuteNonQuery(strSql.ToString(), parameters);
        }

        public int Update(web_siteEntity entity)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE web_site SET ");
            strSql.Append("name=@name,");
            strSql.Append("description=@description,");
            strSql.Append("category_id=@category_id");

            strSql.Append(" WHERE site_id=@site_id");
            SqlParameter[] parameters = {
					new SqlParameter("@site_id",SqlDbType.Int),
					new SqlParameter("@name",SqlDbType.VarChar),
					new SqlParameter("@description",SqlDbType.VarChar),
					new SqlParameter("@category_id",SqlDbType.UniqueIdentifier)
					};
            parameters[0].Value = entity.site_id;
            parameters[1].Value = entity.name;
            parameters[2].Value = entity.description;
            parameters[3].Value = entity.category_id;

            return helper.ExecuteNonQuery(strSql.ToString(), parameters);
        }

        public int UpdateBysite_id(web_siteEntity entity)
        {
            string where = string.Format("site_id='{0}'", entity.site_id);
            entity.site_id = null;
            return Updatepartial(entity, where);
        }

        public int Delete(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("DELETE FROM web_site ");
            if (!string.IsNullOrEmpty(strWhere))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            return helper.ExecuteNonQuery(strSql.ToString());
        }

        public web_siteEntity GetEntity(int primaryKeyId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from web_site ");
            strSql.Append(" where site_id=@primaryKeyId");
            SqlParameter[] parameters = {
					new SqlParameter("@primaryKeyId", SqlDbType.Int)};
            parameters[0].Value = primaryKeyId;
            SqlDataReader dr = helper.ExecuteReader(strSql.ToString(), parameters) as SqlDataReader;
            if (dr.HasRows)
            {
                dr.Read();
                web_siteEntity entity = new web_siteEntity();
                if (!Convert.IsDBNull(dr["site_id"]))
                {
                    entity.site_id = (int)dr["site_id"];
                }
                entity.name = dr["name"].ToString();
                entity.description = dr["description"].ToString();
                if (!Convert.IsDBNull(dr["category_id"]))
                {
                    entity.category_id = (Guid)dr["category_id"];
                }
                dr.Close();
                dr.Dispose();
                return entity;
            }
            else
            {
                dr.Close();
                dr.Dispose();
                return null;
            }
        }

        public object GetCell(string cellName, string where)
        {
            string strSql = string.Empty;
            if (string.IsNullOrEmpty(where))
            {
                strSql = string.Format("SELECT {0} FROM web_site", cellName);
            }
            else
            {
                strSql = string.Format("SELECT {0} FROM web_site Where {1}", cellName, where);
            }
            return helper.ExecuteCell(strSql);
        }

        public DataTable GetTable(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT site_id,name,description,category_id");
            strSql.Append(" FROM web_site ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" WHERE " + strWhere);
            }
            return helper.ExecuteAdapter(strSql.ToString());
        }

        public SortList<web_siteEntity> GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select site_id,name,description,category_id");
            strSql.Append(" FROM web_site ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            SqlDataReader dr = helper.ExecuteReader(strSql.ToString()) as SqlDataReader;
            SortList<web_siteEntity> list = new SortList<web_siteEntity>();
            while (dr.Read())
            {
                web_siteEntity entity = new web_siteEntity();
                if (!Convert.IsDBNull(dr["site_id"]))
                {
                    entity.site_id = (int)dr["site_id"];
                }
                entity.name = dr["name"].ToString();
                entity.description = dr["description"].ToString();
                if (!Convert.IsDBNull(dr["category_id"]))
                {
                    entity.category_id = (Guid)dr["category_id"];
                }
                list.Add(entity);
            }

            dr.Close();
            dr.Dispose();

            return list;
        }


        #region select,add,update the fields that you want to operate.

        public int AddPartial(web_siteEntity entity)
        {
            int reval = 0;
            string sql = string.Empty;
            SqlParameter[] param = DataUtility.BuildAdd(entity, "web_site", ref sql);

            if (param != null)
            {
                reval = helper.ExecuteNonQuery(sql, param);
            }
            return reval;

        }

        public int Updatepartial(web_siteEntity entity, string where)
        {
            int reval = 0;
            string sql = string.Empty;
            SqlParameter[] param = DataUtility.BuildUpdate(entity, "web_site", where, ref sql);

            if (param != null)
            {
                reval = helper.ExecuteNonQuery(sql, param);
            }
            return reval;
        }

        public List<web_siteEntity> GetPartial(string[] selectedFields, string where)
        {
            List<web_siteEntity> list = new List<web_siteEntity>();
            string sql = DataUtility.BuildSelect(selectedFields, "web_site", where);
            if (!string.IsNullOrEmpty(sql))
            {
                DbDataReader reader = helper.ExecuteReader(sql);

                while (reader.Read())
                {
                    web_siteEntity model = new web_siteEntity();
                    foreach (string field in selectedFields)
                    {
                        PropertyInfo propInfo = model.GetType().GetProperty(field);
                        propInfo.SetValue(model, reader[field], null);
                    }

                    list.Add(model);
                }

                reader.Close();
            }
            return list;
        }
        #endregion


        #endregion
    }
}
